package ict.kth.se.model.repository.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import ict.kth.se.exceptions.EntityAlreadyExistsInDBException;
import ict.kth.se.exceptions.EntityDoesntExistInDBException;
import ict.kth.se.model.Role;
import ict.kth.se.model.repository.RoleRepository;

public class JdbcRoleRepository implements RoleRepository{

	private JdbcTemplate jdbcTemplate;
	private RowMapper<Role> rowMapper = new RoleRowMapper();
	
	/**
	 * public constructor
	 * @param dataSource datasource to set jdbctemplate
	 */
	public JdbcRoleRepository(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	/**
	 * Get Role by id
	 * @param id Id of role
	 * @return Found role
	 */
	@Override
	public Role getRoleById(Long id) throws SQLException{
		try{
			String sql = "SELECT * FROM Role WHERE role_id = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, id);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get Role by name
	 * @param name Name of role
	 * @return Found role
	 */
	@Override
	public Role getRoleByName(String name) throws SQLException{
		try{
			String sql = "SELECT * FROM Role WHERE name = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, name);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get all role
	 * @return Found roles
	 */
	@Override
	public List<Role> getAllRoles() throws SQLException{
		try{
			String sql = "SELECT * FROM Role;";
			return jdbcTemplate.query(sql, rowMapper);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method removes one role from database (by it's ID)
	 * @param role The role to be removed
	 * @throws EntityDoesntExistInDBException If role doesn't exist in database
	 * @throws SQLException
	 */
	public void removeRole(Role role) throws EntityDoesntExistInDBException, SQLException{
		if(this.getRoleById(role.getId()) == null)
			throw new EntityDoesntExistInDBException("No such role in database");
		
		String sql = "DELETE FROM Role WHERE role_id = ?;";
		jdbcTemplate.update(sql, role.getId());
	}
	
	/**
	 * This method adds an role in DB
	 * @param role Role to be added
	 * @return Newly created role
	 * @throws EntityAlreadyExistsInDBException If such role already exists in DB (by name)
	 * @throws SQLException
	 */
	public Role createRole(Role role) throws EntityAlreadyExistsInDBException, SQLException{
		//Some controll before insertion
		if(role == null)
			throw new SQLException("Role can't be null.");
		
		//if there is already such role
		if(this.getRoleByName(role.getName()) != null){
			throw new EntityAlreadyExistsInDBException("There is already such role in DB.");
		}
		
		//If not than add it
		KeyHolder keyHolder = new GeneratedKeyHolder();
		//Now we have to define all variables as final to be used in prepared statement
		final String name = role.getName();
		
		final String sql = "INSERT INTO Role (name) VALUES (?);";
		
		jdbcTemplate.update(
			    new PreparedStatementCreator() {
			        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"role_id"});
			            ps.setString(1, name);
			            return ps;
			        }
			    },
			    keyHolder);
		
		//After update add new id and return
		role.setId(keyHolder.getKey().longValue());
		return role;
	}
	
	/**
	 * This method updates an role in DB
	 * @param role Role to be updated
	 * @throws EntityDoesntExistInDBException If such role doesn't exist in DB (by id)
	 * @throws SQLException
	 */
	public void updateRole(Role role) throws EntityDoesntExistInDBException, SQLException{
		//Some controll before updating
		if(this.getRoleById(role.getId()) == null)
			throw new EntityDoesntExistInDBException("No such role in database");
		
		if(role == null || role.getName() == null)
			throw new SQLException("Role or role's name was null");
		
		String sql = "UPDATE Role SET name = ? WHERE role_id = ?;";
		
		jdbcTemplate.update(sql, role.getName(), role.getId());
	}
	
	/**
	 * RowMapper to map role objects 
	 */
	class RoleRowMapper implements RowMapper<Role>{
		public Role mapRow(ResultSet rs, int rowNum) throws SQLException{
			return mapRole(rs);
		}
	}
	
	/**
	 * Maps a row returned from a query of Role to a Role object.
	 * @param rs the result set with its cursor positioned at the current row
	 */
	private Role mapRole(ResultSet rs) throws SQLException {
		Role role = new Role();
		role.setId(rs.getLong("role_id"));
		role.setName(rs.getString("name"));
		return role;
	}
}
